/*

*/
ALTER PROC SP_SPLIT_QUOTATION_LINE_ITEM @SPLIT_FROM_LINEITEM_ID INT,
					@SPLIT_QTY INT,
					@SPLIT_REQ_DOCK_DATE DATETIME,
					@USER_ID VARCHAR(50)
AS
BEGIN
	DECLARE @LINEITEM_QTY INT
	DECLARE @MAX_CHANGE_NO INT
	DECLARE @MAX_LINEITEM_NO INT
	DECLARE @NEW_LINEITEM_ID INT

	--CHECK WHETHER LINE ITEM CAN BE SPLIT OR NOT
	SELECT 	@LINEITEM_QTY = QLI_QTY FROM QUOTATION_ORDER_LINEITEM WHERE LINEITEM_ID = @SPLIT_FROM_LINEITEM_ID
	IF ( @LINEITEM_QTY > @SPLIT_QTY ) --CAN SPLIT
	 BEGIN
		--UPDATE LINE ITEM QTY
		UPDATE QUOTATION_ORDER_LINEITEM SET QLI_QTY = (@LINEITEM_QTY-@SPLIT_QTY), QLI_AMOUNT = (@LINEITEM_QTY-@SPLIT_QTY)*QLI_UNIT_COST, QLI_LAST_UPDATED_BY_ID = @USER_ID, QLI_LAST_UPDATED_BY_NAME = @USER_ID, TIMESTAMP = CURRENT_TIMESTAMP WHERE LINEITEM_ID = @SPLIT_FROM_LINEITEM_ID
		--INSERT INTO CHANGE LINE ITEM TABLE
		SELECT @MAX_CHANGE_NO = MAX(CHANGE_NUMBER) FROM QUOTATION_CHANGE_ORDER_LINEITEM WHERE LINEITEM_ID = @SPLIT_FROM_LINEITEM_ID
		SET @MAX_CHANGE_NO = ISNULL(@MAX_CHANGE_NO,-1) + 1
		INSERT INTO QUOTATION_CHANGE_ORDER_LINEITEM(LINEITEM_ID, QUOTATION_ID, CHANGE_NUMBER, LINE_ITEM_NO, PRODUCT_ID, 
				QTY, UNIT_COST, AMOUNT, REQUESTED_DATE, BOOKING_DATE, SUGG_SCHEDULE_DATE_WITH_SUBSTITUTABLES, SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES,
				SCHEDULE_DATE, REVISED_DATE, IS_CANCEL, STATUS, CHANGE_BY_ID, CHANGE_BY_NAME, TIMESTAMP, TAXABLE, PO_LINE_ITEM_NO, TRANSFER, IS_APPROVED, 
				QUOTED_QTY, QUOTED_DATE, CUSTOMER_PARTNUMBER, LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE )
			SELECT LINEITEM_ID, QUOTATION_ID, @MAX_CHANGE_NO, QLI_LINE_ITEM_NO, PRODUCT_ID,
				QLI_QTY, QLI_UNIT_COST, QLI_AMOUNT, QLI_REQUESTED_DATE, QLI_BOOKING_DATE, QLI_SUGG_SCHEDULE_DATE_WITH_SUBSTITUTABLES, QLI_SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES,
				QLI_SCHEDULE_DATE, QLI_REVISED_DATE, QLI_IS_CANCEL, QLI_STATUS, @USER_ID, @USER_ID, CURRENT_TIMESTAMP, QLI_TAXABLE, QLI_PO_LINE_ITEM_NO, TRANSFER, IS_APPROVED,
				 QLI_QUOTED_QTY, QLI_QUOTED_DATE, QLI_CUSTOMER_PARTNUMBER, QLI_LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE FROM QUOTATION_ORDER_LINEITEM WHERE LINEITEM_ID = @SPLIT_FROM_LINEITEM_ID

		--INSERT NEW LINE ITEM INTO LINE ITEM TABLE
		SELECT @MAX_LINEITEM_NO = MAX(QLI_LINE_ITEM_NO) FROM QUOTATION_ORDER_LINEITEM WHERE QUOTATION_ID = (SELECT QUOTATION_ID FROM QUOTATION_ORDER_LINEITEM WHERE LINEITEM_ID = @SPLIT_FROM_LINEITEM_ID)
		SET @MAX_LINEITEM_NO = ISNULL(@MAX_LINEITEM_NO,0) + 1
		INSERT INTO QUOTATION_ORDER_LINEITEM(QUOTATION_ID, QLI_LINE_ITEM_NO, PRODUCT_ID,
				QLI_QTY, QLI_UNIT_COST, QLI_AMOUNT, QLI_REQUESTED_DATE, QLI_BOOKING_DATE, 
				QLI_STATUS, QLI_ADDED_BY_ID, QLI_ADDED_BY_NAME, TIMESTAMP, QLI_TAXABLE, QLI_DESCRIPTION, QLI_PO_LINE_ITEM_NO, TRANSFER, IS_APPROVED,
				 QLI_QUOTED_QTY, QLI_QUOTED_DATE, QLI_CUSTOMER_PARTNUMBER, QLI_LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE)
			SELECT QUOTATION_ID, @MAX_LINEITEM_NO, PRODUCT_ID,
				@SPLIT_QTY, QLI_UNIT_COST, (@SPLIT_QTY*QLI_UNIT_COST), @SPLIT_REQ_DOCK_DATE, CURRENT_TIMESTAMP, 
				QLI_STATUS, @USER_ID, @USER_ID, CURRENT_TIMESTAMP, QLI_TAXABLE, QLI_DESCRIPTION, QLI_PO_LINE_ITEM_NO, TRANSFER, IS_APPROVED,
				 QLI_QUOTED_QTY, QLI_QUOTED_DATE, QLI_CUSTOMER_PARTNUMBER, QLI_LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE FROM QUOTATION_ORDER_LINEITEM WHERE LINEITEM_ID = @SPLIT_FROM_LINEITEM_ID
		
		SELECT @NEW_LINEITEM_ID = @@IDENTITY
		--INSERT ABOVE NEW LINE ITEM INTO CHANGE LINE ITEM TABLE
		INSERT INTO QUOTATION_CHANGE_ORDER_LINEITEM(LINEITEM_ID, QUOTATION_ID, CHANGE_NUMBER, LINE_ITEM_NO, PRODUCT_ID, 
				QTY, UNIT_COST, AMOUNT, REQUESTED_DATE, BOOKING_DATE, SUGG_SCHEDULE_DATE_WITH_SUBSTITUTABLES, SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES,
				SCHEDULE_DATE, REVISED_DATE, IS_CANCEL, STATUS, CHANGE_BY_ID, CHANGE_BY_NAME, TIMESTAMP, TAXABLE, PO_LINE_ITEM_NO, TRANSFER, IS_APPROVED, 
				QUOTED_QTY, QUOTED_DATE, CUSTOMER_PARTNUMBER, LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE )
			SELECT LINEITEM_ID, QUOTATION_ID, 0, QLI_LINE_ITEM_NO, PRODUCT_ID,
				QLI_QTY, QLI_UNIT_COST, QLI_AMOUNT, QLI_REQUESTED_DATE, QLI_BOOKING_DATE, QLI_SUGG_SCHEDULE_DATE_WITH_SUBSTITUTABLES, QLI_SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES,
				QLI_SCHEDULE_DATE, QLI_REVISED_DATE, QLI_IS_CANCEL, QLI_STATUS, @USER_ID, @USER_ID, CURRENT_TIMESTAMP, QLI_TAXABLE, QLI_PO_LINE_ITEM_NO, TRANSFER, IS_APPROVED,
				 QLI_QUOTED_QTY, QLI_QUOTED_DATE, QLI_CUSTOMER_PARTNUMBER, QLI_LEAD_TIME, REQUESTED_PRICE, CUST_RESALE_PRICE, SUGG_RESALE_PRICE FROM QUOTATION_ORDER_LINEITEM WHERE LINEITEM_ID = @NEW_LINEITEM_ID
	 END
	/*ELSE --CAN NOT SPLIT
	 BEGIN
	 END*/
END
